# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from sqlalchemy import create_engine
from FetchFund import settings
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Text, ForeignKey, DateTime, UniqueConstraint, Index


engine = create_engine("mysql+pymysql://"+settings.MYSQL_USER+":"+settings.MYSQL_PASSWD+"@"+settings.MYSQL_HOST+":"+str(settings.MYSQL_PORT)+"/"+settings.MYSQL_DBNAME,
                       max_overflow=0,  # 超过连接池大小外最多创建的连接
                       pool_size=50,  # 连接池大小
                       pool_timeout=5,  # 池中没有线程最多等待的时间，否则报错
                       pool_recycle=-1  # 多久之后对线程池中的线程进行一次连接的回收（重置）
                       )
Base = declarative_base()

class MysqlAlchemyPipeline:
    def __init__(self):
        Session = sessionmaker(bind=engine)
        self.session = Session()

        Base.metadata.drop_all(engine)
        Base.metadata.create_all(engine)
        
        self.data=[]
        self.codeDict={}
        pass
    
    def process_item(self, item, spider):
        self.codeDict[item['fund_code']]=item['fund_code']
        self.data.append({
            "fund_code":item['fund_code'],
            "fund_name":item['fund_name'],
            "fund_link":item['fund_link'],
            "fund_current_value":item['fund_current_value'],
            "fund_current_date":item['fund_current_date'],
            "fund_total_value":item['fund_total_value'],
            "one_month_rate":item['one_month_rate'],
            "three_month_rate":item['three_month_rate'],
            "six_month_rate":item['six_month_rate'],
            "one_year_rate":item['one_year_rate'],
            "three_year_rate":item['three_year_rate'],
            "total_rate":item['total_rate'],
            "fund_start_time":item['fund_start_time'],
            "fund_manager":item['fund_manager'],
            "fund_size":item['fund_size'],
            "fund_type":item['fund_type'],
            "fund_level":item['fund_level']})
        if len(self.data)>300:
            self._write2db()
        return item
    
    def close_spider(self, spider):
        if len(self.data)>0:
            self._write2db()
        print(len(self.codeDict))
        return
    
    def _write2db(self):
        self.session.execute(
            Fund.__table__.insert(),
            self.data
        )
        self.session.commit()
        self.data.clear()


class Fund(Base):
    __tablename__ = 't_fund'

    id = Column(Integer, primary_key=True,autoincrement=True)
    fund_code = Column(String(16), nullable=True)
    fund_name = Column(String(64), nullable=True)
    fund_link = Column(String(255), nullable=True)
    fund_current_value = Column(String(64), nullable=True)
    fund_current_date = Column(String(32), nullable=True)
    fund_total_value = Column(String(32), nullable=True)
    one_month_rate = Column(String(16), nullable=True)
    three_month_rate = Column(String(16), nullable=True)
    six_month_rate = Column(String(16), nullable=True)
    one_year_rate = Column(String(16), nullable=True)
    three_year_rate = Column(String(16), nullable=True)
    total_rate = Column(String(16), nullable=True)
    fund_start_time = Column(String(32), nullable=True)
    fund_manager = Column(String(32), nullable=True)
    fund_size = Column(String(32), nullable=True)
    fund_type = Column(String(32), nullable=True)
    fund_level = Column(String(32), nullable=True)
